using System;
using System.Data;
using System.IO;
using System.Collections.Generic;
using System.Text;

namespace SistemaLib.sipe.util.io
{
    public class TxtToDataSet
    {

        public static DataSet GetDataSet(string caminoTxt)
        {
            return BuildDataSet(caminoTxt, "Medidas", "\t");
        }

        public static DataSet GetDataSetOleDb(string caminoTxt)
        {
            DataSet ds = new DataSet();
            FileInfo fi = new FileInfo(caminoTxt);

            writeSchema(fi);

            /* You can get connected to driver either by using
            DSN or connection string. Create a connection string
            as below, if you want to use DSN less connection.
            The DBQ attribute sets the path of directory which 
            contains CSV files*/

            string strConnString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + fi.DirectoryName + ";Extensions=asc,csv,tab,txt;Persist Security Info=False";

            string sql_select;
            System.Data.Odbc.OdbcConnection conn;

            //Create connection to CSV file
            conn = new System.Data.Odbc.OdbcConnection(strConnString.Trim());

            //Open the connection 
            conn.Open();

            //Fetch records from CSV
            sql_select = "select * from [" + fi.Name + "]";

            System.Data.Odbc.OdbcDataAdapter obj_oledb_da = new System.Data.Odbc.OdbcDataAdapter(sql_select, conn);
            //Fill dataset with the records from CSV file

            obj_oledb_da.Fill(ds, "Tabla");


            conn.Close();

            return ds;

        }

        private static void writeSchema(FileInfo fi)
        {
            string strFormat = "TabDelimited";

            FileStream fsOutput = new FileStream(fi.DirectoryName + "\\schema.ini", FileMode.Create, FileAccess.Write);
            StreamWriter srOutput = new StreamWriter(fsOutput);
            string s1, s2, s3, s4, s5;
            s1 = "[" + fi.Name + "]";
            s2 = "ColNameHeader=true";
            s3 = "Format=" + strFormat;
            s4 = "MaxScanRows=25";
            s5 = "CharacterSet=OEM";
            srOutput.WriteLine(s1.ToString() + '\n' + s2.ToString() + '\n' + s3.ToString() + '\n' + s4.ToString() + '\n' + s5.ToString());
            srOutput.Close();
            fsOutput.Close();

        }


        #region BuildDataSet
        /// <summary>
        /// method to read a text file into a DataSet
        /// </summary>
        /// <param name="file">file to read from</param>
        /// <param name="tableName">name of the DataTable we want to add</param>
        /// <param name="delimeter">delimiter to split on</param>
        /// <returns>a populated DataSet</returns>
        public static DataSet BuildDataSet(string file, string tableName, string delimeter)
        {
            //create our DataSet
            DataSet domains = new DataSet();
            //add our table
            domains.Tables.Add(tableName);
            try
            {
                //first make sure the file exists
                if (File.Exists(file))
                {
                    //create a StreamReader and open our text file
                    FileStream stream = new FileStream(file, FileMode.Open);
                    StreamReader reader = new StreamReader(stream, Encoding.GetEncoding("iso-8859-15"));
                    //read the first line in and split it into columns
                    string[] columns = reader.ReadLine().Split(delimeter.ToCharArray());
                    //now add our columns (we will check to make sure the column doesnt exist before adding it)
                    foreach (string col in columns)
                    {
                        //variable to determine if a column has been added
                        bool added = false;
                        string next = "";
                        //our counter
                        int i = 0;
                        while (!(added))
                        {
                            string columnName = col;
                            //now check to see if the column already exists in our DataTable
                            if (!(domains.Tables[tableName].Columns.Contains(columnName)))
                            {
                                //since its not in our DataSet we will add it
                                domains.Tables[tableName].Columns.Add(columnName, typeof(string));
                                added = true;
                            }
                            else
                            {
                                //we didnt add the column so increment out counter
                                i++;
                            }
                        }
                    }
                    while (reader.Peek() > -1)
                    {
                        string r = reader.ReadLine();
                        string[] items = r.Split(delimeter.ToCharArray());
                        //split the row at the delimiter
                        domains.Tables[tableName].Rows.Add(items);
                    }

                    ////now we need to read the rest of the text file
                    //string data = reader.ReadToEnd();
                    ////now we will split the file on the carriage return/line feed
                    ////and toss it into a string array
                    //string[] rows = data.Split("\r".ToCharArray());
                    ////now we will add the rows to our DataTable
                    //foreach (string r in rows)
                    //{
                    //    string[] items = r.Split(delimeter.ToCharArray());
                    //    //split the row at the delimiter
                    //    domains.Tables[tableName].Rows.Add(items);
                    //}
                }
                else
                {
                    throw new FileNotFoundException("The file " + file + " could not be found");
                }

            }
            catch (FileNotFoundException ex)
            {
                return null;
            }
            catch (Exception ex)
            {
                return null;
            }
            

            //now return the DataSet
            return domains;
        }
        #endregion


    }
}
